Generated code - Linq to LLBLGen Pro, Function mappings

Preface

A new facility offered by Linq, and thus also by Linq to LLBLGen Pro, is the ability to specify a call to a .NET method or property which is automatically transformed to a database function call or database construct which produces the same result. This section describes this feature in depth, how you can define your own mappings with little effort and also how this facility can be used to enable Full text search on for example SqlServer but also other databases which support Full text search like MySql.

Linq to LLBLGen Pro comes with a large set of pre-mapped functions / database constructs out of the box. These mappings are described briefly in this section. Not all constructs could be re-created on all databases. For example due to its very limited SQL language, it wasn't possible to implement a lot of the .NET methods SQL on Firebird.

Not only class methods can be mapped to a database construct, also properties can be mapped onto database constructs. Per database the pre-defined mappings are created in the DQE of that particular database. All mappings result in a DbFunctionCall element in the final query.

A FunctionMapping is always between a .NET method or property and a database function or construct. This means that if you have a function defined in your database schema and you want to call it inside a Linq query you can do so by defining a mapping between a .NET method and that database function. If such a .NET method doesn't exist, you've got to define one yourself. This method can be empty, as it's not used at runtime. This section contains an example of this where a .NET method is mapped onto a user defined database function.

FunctionMapping and FunctionMappingStore

A mapping between a .NET method or property is defined by creating a FunctionMapping instance and storing it in a FunctionMappingStore instance. Every DQE has a default set of FunctionMapping instances which are stored in the DQE's default FunctionMappingStore. This FunctionMappingStore is obtained automatically by the Linq to LLBLGen Pro provider so you can write a Linq query and use the pre-defined mappings right away.

You can extend this system by specifying your own FunctionMapping instances in a custom FunctionMappingStore instance. Custom mappings take precedence over the pre-defined mappings in the DQE, so if you want to change the pre-defined mappings, you can easily do so by simply specifying a new mapping in a custom FunctionMappingStore instance.

A FunctionMapping instance has a Key and a Pattern. The Key is composed of the following: Method name|Defining type name|Number of parameters. The pattern is a pattern which can be used with a DbFunctionCall, e.g.: "{0} + {1}". The FunctionMapping constructor makes it easy to define a function mapping. For example, to define a database mapping for String.Concat, which accepts 2 parameters, and which should result in operand1 + operand2 in SqlServer, one can use the following FunctionMapping construction: 

FunctionMapping stringConcatMapping = new FunctionMapping(typeof(string), "Concat", 2, "({0} + {1})");
Dim stringConcatMapping As New FunctionMapping(GetType(String), "Concat", 2, "({0} + {1})")

To fill a FunctionMappingStore, it's simply a matter of calling the Add method on the FunctionMappingStore instance to add a FunctionMapping to the FunctionMappingStore. Once a FunctionMappingStore is filled with new mappings, it can be used in all your Linq queries in a thread-safe way, as the store is used on a read-only basis. It's recommended that you build your custom FunctionMappingStore at the start of your application, and after that re-use it in your queries.

Property get calls are mapped with a method name similar to: get_propertyname. An operator overload method is mapped with a method name similar to op_operatorMethod. For the number of parameters, specify 0 for a property getter and 1 for a property which is an indexer. Please see the .NET 2.0+ Runtime Libraries sourcecode for examples of the function mappings defined in the various DQE projects. Function mappings inside DQEs are defined in the DynamicQueryEngine.cs files.
Calling unmapped .NET methods in a query
If you call a .NET method in your query and no FunctionMapping can be found for that method, the method is considered to be in-memory and is threated as a normal in-memory method call, as described in the section Calling an in-memory method in the projection.
Passing a custom FunctionMappingStore to the query
A custom FunctionMappingStore can be passed to the query by passing them to the constructor of the LinqMetaData class which is then used in the query. You can also set the custom FunctionMappingStore by setting the CustomFunctionMappings property of the LinqMetaData instance.

If you want to switch custom function mappings between queries created with the same LinqMetaData instance, you can: the properties called on the LinqMetaData (e.g. metaData.Customer) which produce the Queryable elements, grab the custom FunctionMappingStore instance set at that point. So if you set CustomFunctionMappings to null / Nothing in between the creation of queries using the same LinqMetaData instance, the query created after CustomFunctionMappings was set to null on the LinqMetaData instance won't use the CustomFunctionMappings.
Example of custom FunctionMapping usage
The following example shows how to define a custom mapping of a new .NET method onto a custom database function and how to use it in a query. As the .NET method is mapped onto a database element, we can use it inside the query and we're not limited to have it inside the projection. The example uses it inside the projection, but you can use it in a Where clause as well for example, as the method due to the mapping converts to a SQL fragment.

SQL function:

-- SQL function stored in the target db. 
-- It calculates the order total of a given order.
ALTER   FUNCTION fn_CalculateOrderTotal(@orderID int, @useDiscounts bit)
RETURNS DECIMAL
AS
BEGIN
	DECLARE @toReturn DECIMAL

	SELECT @toReturn = SUM((UnitPrice-(Discount * @useDiscounts)) * Quantity)
	FROM [Order Details] 
	WHERE OrderID = @orderID
	GROUP BY OrderID

	RETURN @toReturn
END

.NET code using the function:

/// Class which is used to specify the call to the database function. We'll map
/// the function inside it, CalculateOrderTotal, to the database function.
public class NorthwindFunctions
{
    /// <summary>
    /// Calculates the order total.
    /// </summary>
    /// <param name="orderId">The order id.</param>
    /// <param name="useDiscounts">if set to <c>true</c> [use discounts].</param>
    /// <returns></returns>
    public static decimal CalculateOrderTotal(int orderId, bool useDiscounts)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return 0.0M;
    }
}


/// Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
/// fn_CalculateOrderTotal, the database function.
public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
        // define the mapping. SQLServer 2000 needs the schema to be present for the function call, 
		// so we specify that as well.
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "CalculateOrderTotal", 2, 
                        "fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"));
    }
}




//...


// these constructs are now allowing the developer to actually call fn_CalculateOrderTotal from within 
// the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
// the LinqMetaData first:
metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the orders with the total calculated by the function inside the database
var q = from o in metaData.Order
        where o.CustomerId == "CHOPS"
        select new { o.OrderId, OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, true) };
''' Class which is used to specify the call to the database function. We'll map
''' the function inside it, CalculateOrderTotal, to the database function.
Public Class NorthwindFunctions
    ''' <summary>
    ''' Calculates the order total.
    ''' </summary>
    ''' <param name="orderId">The order id.</param>
    ''' <param name="useDiscounts">if set to <c>true</c> [use discounts].</param>
    ''' <returns></returns>
    Public Shared Function CalculateOrderTotal(orderId As Integer, useDiscounts As Boolean) As Decimal
        ' empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        Return CDec(0.0)
    End Function
End Class

''' Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
''' fn_CalculateOrderTotal, the database function.
Public Class NorthwindFunctionMappings 
	Inherits FunctionMappingStore

    Public Sub New()
        ' define the mapping. SQLServer 2000 needs the schema to be present for the function call, 
		' so we specify that as well.
        Me.Add(New FunctionMapping(GetType(NorthwindFunctions), "CalculateOrderTotal", 2, _
                        "fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"))
    End Sub
End Class


' ...

' these constructs are now allowing the developer to actually call fn_CalculateOrderTotal from within 
' the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
' the LinqMetaData first:
metaData.CustomFunctionMappings = New NorthwindFunctionMappings()
' fetch the orders with the total calculated by the function inside the database
Dim q = From o In metaData.Order _
        Where o.CustomerId = "CHOPS" _
        Select New With _
        { _
            .OrderId = o.OrderId, _
            .OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, True) _
		  }

Full-text search

Using custom FunctionMapping instances, it's possible to define the Full-text search methods required in the query. We opted for this route instead of adding hard-coded codepaths for Full-text search, as for every database full text search is implemented differently.

On SqlServer, we can define a simple method in .NET code to be able to specify the method call, and a mapping to CONTAINS(). We then can use the .NET method to specify a full text search:

/// Class which is used to specify the call to the CONTAINS construct. 
public class NorthwindFunctions
{
    public static bool FullTextSearch(string fieldToSearch, string toFind)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return true;
    }
}

/// Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS()
public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
         // FullTextSearch(2) on 1 field
         this.Add(new FunctionMapping(typeof(NorthwindFunctions), "FullTextSearch", 2, "CONTAINS({0}, {1})"));
    }
}


//...

// these constructs are now allowing the developer to actually call CONTAINS from within 
// the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
// the LinqMetaData first:
metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the employees which have 'BA' in their Notes field which is Full text search enabled.
var q = from e in metaData.Employee
        where NorthwindFunctions.FullTextSearch(e.Notes, "BA")
        select e;
''' Class which is used to specify the call to the Contains construct
Public Class NorthwindFunctions
    Public Shared Function FullTextSearch(fieldToSearch As String, toFind As String) As Boolean
        ' empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        Return True
    End Function
End Class

''' Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS
Public Class NorthwindFunctionMappings 
	Inherits FunctionMappingStore

    Public Sub New()
         ' FullTextSearch(2) on 1 field
         Me.Add(New FunctionMapping(GetType(NorthwindFunctions), "FullTextSearch", 2, "CONTAINS({0}, {1})"))
    End Sub
End Class


' ...

' these constructs are now allowing the developer to actually call CONTAINS from within 
' the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
' the LinqMetaData first:
metaData.CustomFunctionMappings = New NorthwindFunctionMappings()
// fetch the employees which have 'BA' in their Notes field which is Full text search enabled.
Dim q = From e In metaData.Employee _
        Where NorthwindFunctions.FullTextSearch(e.Notes, "BA") _
        Select e

Supported default method / property mappings to functions

The following method/property mappings are supported in SQL. They're grouped per .NET type which defines the method/property and then the list of methods/properties which are supported. If there's no mapping defined for a given database for a given method/property, it's specified with the method or property. The classes mentioned have more methods / properties than the list shows: if a method or property isn't mentioned in the list, there's no mapping of that method or property to a database construct in any supported database. Most of the time, this is because there's no database function equivalent, it needs a .NET object to function properly (e.g. a comparer) etc.

With 'supported' is meant: usable in all areas of a linq query. A method or property which isn't mentioned in the list is still supported as an in-memory method/property, but solely in the last projection as it otherwise has to be ran on the database.
All returned indexes are floored to 0-based indexes. So if a database function starts with 1 for the first element in a set, 0 is returned if the function returns as index 1. The types of the arguments of the overload supported are given between brackets.
Array methods / properties defined by System.Array
The following Array method / properties are supported.

Boolean methods / properties defined by System.Boolean
Boolean doesn't really have any of its methods/properties mapped, though it has a special method mapped which is actually an operator: Negate. This mapping creates a NOT on the boolean.
Char methods / properties defined by System.Char
Char doesn't really have any of its methods/properties mapped, though it has a special method mapped which is used to convert the char inside the database to a unicode number: ToUnicode. The usage of this method is to convert a char to an int. This is required as char constants are stored as integers inside .NET programs and comparing a char value in the database with a constant will result in a compare with an integer, which is then passed to the database as-is, thus as an integer. To avoid that the compare fails, the char value is converted to unicode inside the database using the function mapped as Char.ToUnicode(1).

The integer value isn't converted to char, because the expression tree contains a convert from char to int, so the only way to handle this in general is to convert that conversion at that spot with the parameters at hand.

Not on: Firebird
Convert methods / properties defined by System.Convert
The Convert class is a class which can convert elements from one type to the other. The following methods and properties are supported. Only the overloads which accept a single value are supported. If one needs specific formatting, please create a custom mapping. Conversions to Char use the database default's length. This could lead to trailing spaces. Conversions to String use the maximum length of unicode variable strings, e.g. nvarchar(4000).

DateTime methods/properties, defined by System.DateTime
The following DateTime functions are supported.  The fraction part in the double typed value passed into the Add* methods is discarded in most databases. Passing 1.4 for example will add a single day when calling AddDays(1.4).

The property 'Now' isn't mapped, although it  could be mapped onto a database function easily. The problem is that with 'Now', it's unclear if the client date is meant or the server date. If the property would be mapped onto a server function, the server date/time would be used in the query, while if it wouldn't get a mapping, the client date/time would be used. If one wants a server date compare, create an extension method for DateTime and map that method in a custom mapping to the database function to retrieve the current date, e.g. GETDATE() on sqlserver.

The following properties are supported

Decimal methods/properties, defined by System.Decimal
The following Decimal methods/properties are supported. The various Totype methods aren't supported, though one could use the Convert.method mappings instead.

String methods/properties, defined by System.String
The following string methods/properties are supported. Most of them don't work on ntext/text/clob fields due to the use of LEN/REVERSE etc. functions.  Often, the database equivalents use a function which determines the length of the string. This is often done by LEN() or similar function. These functions in general don't report trailing blanks, so LEN('Foo  ') will be '3', not 5.

The following properties are supported

Object method/properties, defined by System.Object
The Object properties/methods aren't really defined as methods/properties of object. For example 'ToString()' is overloaded in a lot of classes. ToString is converted in all classes as a call to Convert.ToString(value).
The following artificial methods are mapped on Object. These methods are used by the framework to produce a proper operation. If you want to overrule these functions, you have to add mappings of these functions to object in your custom mappings.

Math method / properties, defined by System.Object
The following Math methods are supported. We only map 'Pow' as the VB.NET language has a Power operator: ^ and therefore we need to map this function. If a developer needs more functions mapped, s/he should create a custom mapping.

Remarks and limitations

The following remarks and limitations have to be taken into account when using function mappings.


LLBLGen Pro v3.1 documentation. ©2011 Solutions Design